from bottle import route, post, request
from jinja2 import Environment, FileSystemLoader

import re
import sys

class result_controller:
  
  @post("/result/<examination_year>/<examination_number>")
  @post("/result/<examination_year>/")
  @post("/result")
  def result(examination_year = 0, examination_number = 0):
  
    # just for that sick reason of javascript being disabled
    if (examination_year == 0):
      examination_year = str(request.forms['exam_year'])
      
    if (examination_number == 0):
      examination_year = str(request.forms['exam_number'])
    
    # get & validate the pin
    pin = str(request.forms['pin'])

    """
    pattern = re.compile("^\d$")
    if pattern.search(pin) != None or len(pin) != 5: 
      # PIN invalid
      return "Invalid PIN."
    """
    
    card_info = card_repository.get_card_info(pin)
    
    # card does not exist in the database
    if card_info == None:
      return "Card does not exist"
    
    # setup environment for templates
    env = Environment(loader=FileSystemLoader(sys.path[0] + '/templates'))
    
    # apply domain rules to the retrieved card information
    status = result_controller.card_biz_process(card_info, examination_number, examination_year)
    
    # if one the domain rules failed, show and error
    if status != None  and status['status'] == 'error':
      template = env.get_template(status['template'])
      return template.render()
      # return status['template']

    result = None

    # here, the card info is valid so get the candidate's result
    # result = mysql_shard_result_repository.get_result(str(examination_number))
    result = mysql_memory_result_repository.get_result(examination_number)
    # result = memcache_result_repository.get_result(examination_number)
    
    # if the examination number is not found in the database
    if result == None:
      # template = env.get_template('/error/examination_number_not_exist.html')
      # return template.render()   
      return "not found"
    
    if result['status'] in ['valid', '1', 1, True]:

      # pair card and examination number
      # would have loved to keep the connection to the card db open but Nah.
      result_controller.pair_card_and_candidate(card_info, result)

      # return "valid"
      template = env.get_template('/result/show_valid.html')
      return template.render(result)
  
    elif result['status'] in ['invalid', '0', 0, False]:
      return "invalid"
      # template = env.get_templat('/result/show_invalid')
      # return template.render()    
    
    return "ok!?"
  
  # abstract or extract to class in real implementation
  def card_biz_process(card_info, examination_number, examination_year):
    # print(card_info)
    # implements logic stuff like 
    # - is the card for use e.g. expiration date check
        
    # if used, examination number of the candidate who used the card == examination number in the request
    if card_info["used"] == "1" and card_info["usedBy"] != examination_number:
      return { 'status': 'error', 'template': '/error/card_used_exam_numbers_no_match.html' }
      
    if card_info["used"] == "1" and card_info["usedBy"] == examination_number and card_info["numberOfUsages"] >= 5:
      return { 'status': 'error', 'template': '/error/card_usage_exceeded.html' }

  def pair_card_and_candidate(card_info, result):
    card_id = card_info['Id']
    examination_number = result['examination_number']
    
    shard = card_sharding_strategy.get_shard(card_info['pin'])
    
    manager = db_conn_manager()
    connection = manager.connect(shard['host'], shard['user'], shard['passwd'], shard['db'])
    cursor = connection.cursor (cursor_class=MySQLCursorDict)
    cursor.execute("UPDATE card SET used = {0}, usedBy = '{1}', numberOfUsages = numberOfUsages + 1 where Id = {2}".format(True, examination_number, card_id))
    cursor.close()
    connection.close()
    
    return

class card_sharding_strategy:

  def get_shard(pin):
    
    # some defaults
    defaults = { 'host' : 'localhost', 'user' : 'root', 'passwd' : '' }
    
    # get the last digit of the PIN
    str_pin_last_digit = str(pin)[-1:]
    if str_pin_last_digit in ["1", "2", "3"]:
      # (1,2,3) go to shard 1
      defaults.update({ "db": '_jamb_card_shard_1' })
      return defaults
    elif str_pin_last_digit in ["4", "5", "6"]:
      # (4,5,6) go to shard 2
      defaults.update({ "db": '_jamb_card_shard_2' })
      return defaults
    elif str_pin_last_digit in ["7", "8" ,"9" ,"0"]:
      # (7,8,9,0) go to shard 3
      defaults.update({ "db": '_jamb_card_shard_3' })
      return defaults
  
  
class db_conn_manager:  
  connection = None
  
  def connect(self, host, user, passwd, db):    
    import mysql.connector
    self.connection = mysql.connector.connect(db=db, passwd=passwd, user=user, host=host)
    # self.connection = MySQLdb.connect (host, user, passwd, db)
    return self.connection
  
  def disconnect(self):
    self.connection.close()


import mysql.connector

class MySQLCursorDict(mysql.connector.cursor.MySQLCursor):

  def fetchone(self):
    row = self._fetch_row()
    
    if row:
      return dict(zip(self.column_names, self._row_to_python(row)))
    return None


class card_repository:
    
  def get_card_info(pin):
    # import MySQLdb as MySQLdb
    
    # determine the database this request will go to based on the 
    # CardDB shard strategy on the card number using in this case, 
    # the last digit of the PIN    
    shard = card_sharding_strategy.get_shard(pin)
      
    conn_mgr = db_conn_manager()
    connection = conn_mgr.connect(shard['host'], shard['user'], shard['passwd'], shard['db'])
    
    # prepare a cursor object using cursor() method
    cursor = connection.cursor (cursor_class=MySQLCursorDict)

    # execute the SQL query using execute() method.
    cursor.execute ("SELECT * FROM card WHERE pin = '{0}' LIMIT 1".format(pin))

    # fetch a single row using fetchone() method.
    row = cursor.fetchone ()
    
    # close the cursor object
    cursor.close ()
    
    connection.close()
        
    return row
    
    
class result_sharding_strategy:

  def get_shard(examination_number):
    # some defaults
    defaults = { 'host' : 'localhost', 'user' : 'root', 'passwd' : '' }
    
    # get the last digit of the Examination number
    str_exam_number_last_digit = str(examination_number)[-1:]
    
    if str_exam_number_last_digit in ["1", "2", "3"]:
      # (1,2,3) go to shard 1
      defaults.update({ "db": '_jamb_result_shard_1' })
      return defaults
    elif str_exam_number_last_digit in ["4", "5", "6"]:
      # (4,5,6) go to shard 2
      defaults.update({ "db": '_jamb_result_shard_2' })
      return defaults
    elif str_exam_number_last_digit in ["7", "8" ,"9" ,"0"]:
      # (7,8,9,0) go to shard 3
      defaults.update({ "db": '_jamb_result_shard_3' })
      return defaults
    

# or mysql_memory_shard_result_repository if using the memory storage engine
class mysql_shard_result_repository:
  def get_result(examination_number):
    # import MySQLdb as MySQLdb
    
    shard = result_sharding_strategy.get_shard(examination_number)
    
    conn_mgr = db_conn_manager()
    connection = conn_mgr.connect(shard['host'], shard['user'], shard['passwd'], shard['db'])
    
    # prepare a cursor object using cursor() method
    cursor = connection.cursor (cursor_class=MySQLCursorDict)
  
    # execute the SQL query using execute() method.
    cursor.execute ("SELECT * FROM result WHERE examination_number = '{0}' LIMIT 1" . format(examination_number))

    # fetch a single row using fetchone() method.
    row = cursor.fetchone ()
    
    # close the cursor object
    cursor.close ()
    
    connection.close()
        
    return row
    

# none-sharded    
class mysql_memory_result_repository:
  def get_result(examination_number):
    
    conn_mgr = db_conn_manager()
    connection = conn_mgr.connect('localhost', 'root', '', '_jamb_result_memory')
    
    # prepare a cursor object using cursor() method
    cursor = connection.cursor (cursor_class=MySQLCursorDict)
    
    # execute the SQL query using execute() method.
    cursor.execute ("SELECT * FROM result WHERE examination_number = '{0}' LIMIT 1" . format(examination_number))

    # fetch a single row using fetchone() method.
    row = cursor.fetchone ()
    
    # close the cursor object
    cursor.close ()
    
    connection.close()
        
    return row
    
    
class memcache_result_repository:   
  
  def get_result(examination_number):
    import memcache as memcache   
    mc = memcache.Client(servers=['127.0.0.1:11211'], debug=1)
    
    """
    mc.set(examination_number, { 
      'examination_number': examination_number, 
      'candidate_name' : 'Abdulkadir Nma Abdulkadir', 
      'result1':"Use of English = 50", 
      'result2':"Mathematics = 90", 
      'result3':"Physics = 90", 
      'result4':"Chemistry = 90", 
      'aggregate':"90", 
      'status': '1' 
    })
    """
    result = mc.get(str(examination_number))
    print(result)
    return result


class memecache_result_sharding_strategy:

  def get_server(examination_number):
  
    # testing as I have only 1 instance of memcache
    return { "server": '127.0.0.1:11211' }
    
    # some defaults
    defaults = {}
    
    # get the last digit of the Examination number
    str_exam_number_last_digit = str(examination_number)[-1:]
    
    if str_exam_number_last_digit in ["1", "2", "3"]:
      # (1,2,3) go to shard 1
      defaults.update({ "server": '127.0.0.1:11211' })
      return defaults
    elif str_exam_number_last_digit in ["4", "5", "6"]:
      # (4,5,6) go to shard 2
      defaults.update({ "server": '127.0.0.2:11211' })
      return defaults
    elif str_exam_number_last_digit in ["7", "8" ,"9" ,"0"]:
      # (7,8,9,0) go to shard 3
      defaults.update({ "server": '127.0.0.3:11211' })
      return defaults
      
    
class memcache_shard_result_repository:
  import memcache as memcache
  
  def get_result(examination_number):
    import memcache as memcache   
    
    server = memcache_result_sharding_strategy.get_server(examination_number)['server']
    mc = memcache.Client(servers=[server], debug=1)
    
    # might shard too
    result = mc.get(examination_number)
    return result